Option Explicit On

Imports MySql.Data.MySqlClient
Imports Microsoft.Office.Interop
Imports System.Threading
Public Class frmrpt_loading
    Dim mysql As MySqlConnection = main_form.mysqlconection
    Dim mysql1 As MySqlConnection = main_form.mysqlconection1
    Dim nickagent As String
    Dim mySqlCommand As New MySqlCommand
    Dim mySqlAdaptor As New MySqlDataAdapter
    Dim mySqlReader As MySqlDataReader
    Dim inbtIndex As Integer

    Dim mySqlCommand1 As New MySqlCommand
    Dim mySqlAdaptor1 As New MySqlDataAdapter
    Dim mySqlReader1 As MySqlDataReader
    Public idvoymain As String
    Dim respone As Object
    Dim idvoyn As String
    Dim idvoys As String
    Public Shared idbooking As String
    Public Shared idborrow As String

    Public Shared idcontainer As String


    Dim ctnname As String
    Dim typectn As String

    Dim textDate As String
    Dim stringDate() As String
    Dim stringhour() As String
    Dim sumtxt As String
    Dim sumint As Double
    Dim agentline As String

    Dim sumctn40 As Integer = 0
    Dim sumctn20 As Integer = 0
    Dim datecheck As String
    Dim stringDate1() As String



    Dim idshipping As String = ""
    Public Delegate Sub DelegateSub(ByVal x As Integer)
    Private Sub frmrpt_loading_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        mysql.Close()
        If mysql.State = ConnectionState.Closed Then
            mysql.Open()
        End If
        mySqlCommand.CommandText = "Select * from booking where BCHECKLOAD ='TRUE' and BVOYAGE ='" & frmsearch_voyage1.idvoyage & "' order by booking.BSHIP;"
        ' mySqlCommand.CommandText = 
        mySqlCommand.Connection = mysql
        mySqlAdaptor.SelectCommand = mySqlCommand
        sumctn40 = 0
        sumctn20 = 0
        Try
            mySqlReader = mySqlCommand.ExecuteReader
            ListView1.Items.Clear()
            While (mySqlReader.Read())
                With ListView1.Items.Add(mySqlReader("BSHIPNAME"))
                    .SubItems.Add(mySqlReader("BSHIP"))
                End With

            End While

        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try




    End Sub


    Private Sub showResult(ByVal Num As Integer)
        If Label10.InvokeRequired Then
            Dim dlg As New DelegateSub(AddressOf showResult)
            Me.Invoke(dlg, Num)

        Else
            CircularProgress1.IsRunning = False

        End If
    End Sub

    Private Sub ButtonX1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonX1.Click

            FolderBrowserDialog1.Description = "Pick Folder to store Excecl files"
            FolderBrowserDialog1.ShowNewFolderButton = True
            FolderBrowserDialog1.SelectedPath = "C:\"
            If FolderBrowserDialog1.ShowDialog() = Windows.Forms.DialogResult.OK Then
                Try
                    Dim t = New Thread(New ThreadStart(AddressOf excelReport))
                    t.Start()
                    CircularProgress1.IsRunning = True
                Catch ex As Exception

                End Try
            End If



    End Sub
    Private Sub excelReport()

        Dim pathExcel As String
        Dim count As Integer = 7
        Dim dateout As String = ""
        Dim ctntype As String = ""
        pathExcel = FolderBrowserDialog1.SelectedPath
        Dim excelapp As New Excel.Application
        Dim excelbooks As Excel.Workbook
        Dim excelsheets As Excel.Worksheet
        excelbooks = excelapp.Workbooks.Add

        excelsheets = CType(excelbooks.Worksheets(1), Excel.Worksheet)
        excelsheets.Name = "V.S" + frmsearch_voyage1.idvoyage.ToString
        With excelsheets
            .PageSetup.PaperSize = Excel.XlPaperSize.xlPaperA4
            .PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape


            Try
                '.Shapes.AddPicture("\\ykpserver55\DB\movement.png", False, True, 1, 1, 150, 80)
            Catch ex As Exception

            End Try
            .Range("A1").ColumnWidth = 3.57
            .Range("B1").ColumnWidth = 22
            .Range("C1").ColumnWidth = 22
            .Range("D1").ColumnWidth = 46
            .Range("E1").ColumnWidth = 29
            .Range("F1").ColumnWidth = 36
            .Range("G1").ColumnWidth = 28
            .Range("H1").ColumnWidth = 26
            .Range("I1").ColumnWidth = 23
    


            .Range("A1:M500").Font.Name = "Arial"
            .Range("A1:M500").Font.Size = 12

            .Rows("1:1").rowheight = 38
            .Rows("2:500").rowheight = 27


            With .Range("D1:F1")
                .Merge()
                .Font.Size = 20
                .Value = "LOADING LIST V.S" + frmsearch_voyage1.idvoyage.ToString
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("B2:C2")
                .Merge()
                .Value = "YKP MARINE SERVICE CO., LTD."
                .Font.Size = 14
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
                .Font.Bold = True
            End With
            With .Range("B3:C3")
                .Merge()
                .Value = "87/1 M.5 T.Boonumron, Kantang, Trang, Thailand  "
                .Font.Size = 14
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
                .Font.Bold = True
            End With
            With .Range("A5:A6")
                .Merge()
                .Value = "No."
                .VerticalAlignment = Excel.XlVAlign.xlVAlignBottom
                .HorizontalAlignment = Excel.Constants.xlLeft
                .Font.Bold = True
            End With
            With .Range("B5:B6")
                .Merge()
                .Value = "ETD from kantang "
                .VerticalAlignment = Excel.XlVAlign.xlVAlignBottom
                .HorizontalAlignment = Excel.Constants.xlLeft
                .Font.Bold = True
            End With

            With .Range("C5:C6")
                .Merge()
                .Value = "SHIPPING"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignBottom
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With

            With .Range("D5:D6")
                .Merge()
                .Value = "Name of Cargo Owner"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignBottom
                .HorizontalAlignment = Excel.Constants.xlLeft
                .Font.Bold = True
            End With



            With .Range("E5:E6")
                .Merge()
                .Value = "MOTHER VESSEL"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignBottom
                .HorizontalAlignment = Excel.Constants.xlLeft
                .Font.Bold = True


            End With

            With .Range("F5:F6")
                .Merge()
                .WrapText = True
                .Value = "From " + Environment.NewLine + "Kantang Thailand " + Environment.NewLine + "to Penang"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignBottom
                .HorizontalAlignment = Excel.Constants.xlLeft
                .Font.Bold = True
            End With

            With .Range("G5:G6")
                .Merge()
                .WrapText = True
                .Value = "Conveyance"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignBottom
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With


            With .Range("H5:H6")
                .Merge()
                .WrapText = True
                .Value = "Number of container"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignBottom
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With

            With .Range("I5:I6")
                .Merge()
                .WrapText = True
                .Value = "Booking No."
                .VerticalAlignment = Excel.XlVAlign.xlVAlignBottom
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With




            .Range("A5:I6").Borders.Weight = 2 ' xlThin

            mysql1.Close()
            If mysql1.State = ConnectionState.Closed Then
                mysql1.Open()
            End If
            mySqlCommand.CommandText = "Select VOYDATEES,BSHIPNAME,conname,BINCOTERM,VOYVESNAMES,place,BINVOICE,BNO,BCTNNO,BCTNTYPE,BREMARK,BNFORWARDERNAME,BMOTH from voyage join booking on voyage.VOYAGEID = booking.BVOYAGE left join booking_bl on booking.BOOKINGID = booking_bl.booking_id where BCHECKLOAD ='True' and BVOYAGE = '" & frmsearch_voyage1.idvoyage & "'  order by  conname ;"
            ' mySqlCommand.CommandText = 
            mySqlCommand.Connection = mysql1
            mySqlAdaptor.SelectCommand = mySqlCommand

            Try
                mySqlReader = mySqlCommand.ExecuteReader
                Dim number As Integer = 1
                count = 7
                Dim sumctn As Integer = 0
                While (mySqlReader.Read())

                    .Range("A" + count.ToString + ":I" + count.ToString).Borders.Weight = 2 ' xlThin

                    With .Range("A" + count.ToString + ":A" + count.ToString)
                        .Merge()
                        .NumberFormat = "@"
                        .Value = number

                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                    End With
                    number += 1
                    With .Range("B" + count.ToString + ":B" + count.ToString)

                        .NumberFormat = "@"
                        .Merge()
                        .Value = mySqlReader("VOYDATEES")
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                        dateout = mySqlReader("VOYDATEES")
                    End With

                    With .Range("C" + count.ToString + ":C" + count.ToString)
                        .NumberFormat = "@"
                        .Merge()
                        .Value = mySqlReader("BNFORWARDERNAME")
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlLeft
                    End With

                    With .Range("D" + count.ToString + ":D" + count.ToString)
                        .Merge()
                        .Value = mySqlReader("BSHIPNAME")
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlLeft
                    End With

                    With .Range("E" + count.ToString + ":E" + count.ToString)
                        .NumberFormat = "@"
                        .Merge()
                        .Value = mySqlReader("BMOTH")
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlLeft
                    End With

                    With .Range("F" + count.ToString + ":F" + count.ToString)
                        .NumberFormat = "@"
                        .Merge()
                        .Value = mySqlReader("VOYVESNAMES")
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlLeft
                    End With
                    With .Range("G" + count.ToString + ":G" + count.ToString)
                        .NumberFormat = "@"
                        .Merge()
                        .Value = mySqlReader("place")
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                    End With

                    With .Range("H" + count.ToString + ":H" + count.ToString)
                        .NumberFormat = "@"
                        .Merge()
                        .Value = mySqlReader("BCTNNO") + " X " + mySqlReader("BCTNTYPE")
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                    End With
                    sumctn += CInt(mySqlReader("BCTNNO"))
                    ctntype = mySqlReader("BCTNTYPE")
                    With .Range("I" + count.ToString + ":I" + count.ToString)
                        .NumberFormat = "@"
                        .Merge()
                        .Value = mySqlReader("BNO")
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                    End With
                    count += 1
                End While




                count += 1

                With .Range("H" + count.ToString + ":H" + count.ToString)
                    .Merge()
                    .Value = sumctn.ToString + " X " + ctntype.ToString
                    .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                    .HorizontalAlignment = Excel.Constants.xlCenter
                    .Font.Underline = Excel.XlUnderlineStyle.xlUnderlineStyleDouble

                End With
            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try




        End With

        Try
            excelbooks.SaveAs(pathExcel.ToString + "\Loading_list_V.S" + frmsearch_voyage1.idvoyage.ToString + ".xlsx")

            excelbooks.Close()
            excelapp.Quit()
            System.Runtime.InteropServices.Marshal.ReleaseComObject(excelapp)
            excelbooks = Nothing
            excelsheets = Nothing
            excelapp = Nothing
            Dim proc As System.Diagnostics.Process

            For Each proc In System.Diagnostics.Process.GetProcessesByName("EXCEL")
                proc.Kill()
            Next
            MsgBox("Report Complete")


        Catch ex As Exception

            excelbooks.Close()
            excelapp.Quit()
            System.Runtime.InteropServices.Marshal.ReleaseComObject(excelapp)
            excelbooks = Nothing
            excelsheets = Nothing
            excelapp = Nothing
            Dim proc As System.Diagnostics.Process

            For Each proc In System.Diagnostics.Process.GetProcessesByName("EXCEL")
                proc.Kill()
            Next


        End Try
        showResult(200)
        CircularProgress1.IsRunning = False


    End Sub

    Private Sub ListView1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles ListView1.Click


        idshipping = Trim(ListView1.SelectedItems(0).SubItems(1).Text)

    End Sub
End Class